﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace PublishData.BLL
{
    class ExcelImporter
    {
        private string fileName;
        public ExcelImporter(string fileName)
        {
            this.fileName = fileName;
        }

        /// <summary>
        /// Lấy danh sách các sheet có trong bảng
        /// </summary>
        /// <returns></returns>
        public List<string> DanhSachSheet()
        {
            try
            {
                OleDbConnection cn = CreateConnection();
                cn.Open();
                DataTable dt = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                    new object[] { null, null, null, "Table" });
                cn.Close();
                List<string> list = new List<string>();
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(dr["TABLE_NAME"].ToString());
                }
                cn.Dispose();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable LayDuLieuSheet(string tenSheet)
        {
            OleDbConnection cn = CreateConnection();
            cn.Open();
            string cmd = String.Format("SELECT * FROM [{0}$]",tenSheet);
            OleDbDataAdapter adap = new OleDbDataAdapter(cmd, cn);
            DataTable dt = new DataTable();
            adap.Fill(dt);
            cn.Close();
            cn.Dispose();
            return dt;
        }

        private OleDbConnection CreateConnection()
        {
            if (fileName.Contains("xlsx"))
                return new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;Data Source=" + fileName
                 + @";Extended Properties=Excel 12.0;");
            else
                return new OleDbConnection(string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=Yes""", fileName));
        }
    }
}
